package com.dao.Impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.MusicDao;
import com.entity.Music;
import com.entity.Data.PagerData;
import com.entity.Query.MusicQuery;
import com.util.MyUtil;


@Repository
public class MusicDaoImpl extends BaseDao implements MusicDao{

	@Override
	public List<Map<String, Object>> queryMusic() {
		String sql ="select mu.musicId,mu.musicName,mu.timeLength,mu.musicMp3,mu.musicPhoto,"
				+ "mut.musicTypeName,sp.specialName,mul.musicListName,si.singerName "
				+ "from music as mu join musictype as mut on mu.musicTypeId=mut.musicTypeId "
				+ "join special as sp on mu.specialId=sp.specialId "
				+ "join musiclist as mul on mu.musicListId=mul.musicListId "
				+ "join singer as si on mu.singerId=si.singerId";
		return executeQuery(sql, null);
	}

	@Override
	public Map<String, Object> queryMusicId(int musicId) {
		String sql ="select mu.musicId,mu.musicName,mu.timeLength,mu.musicMp3,mu.musicPhoto,mu.musicTypeId,mu.specialId,mu.musicListId,mu.singerId,"
				+ "mut.musicTypeName,sp.specialName,mul.musicListName,si.singerName "
				+ "from music as mu join musictype as mut on mu.musicTypeId=mut.musicTypeId "
				+ "join special as sp on mu.specialId=sp.specialId "
				+ "join musiclist as mul on mu.musicListId=mul.musicListId "
				+ "join singer as si on mu.singerId=si.singerId where musicId=?";
		Object[] params={musicId};
		return executeQueryMap(sql, params);
	}

	@Override
	public int addMusic(Music music) {
		String sql ="insert into music(musicName,timeLength,musicMp3,musicPhoto,musicTypeId,specialId,musicListId,singerId)values(?,?,?,?,?,?,?,?)";
		Object[] params={music.getMusicName(),music.getTimeLength(),music.getMusicMp3(),music.getMusicPhoto(),
				music.getMusicTypeId(),music.getSpecialId(),music.getMusicListId(),music.getSingerId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int editMusic(Music music) {
		String sql ="update music	set musicName=?,timelength=?,"
				+ "musicTypeId=?,specialId=?,musicListId=?,singerId=? 	where musicId=?";
		Object[] params={music.getMusicName(),music.getTimeLength(),
				music.getMusicTypeId(),music.getSpecialId(),music.getMusicListId(),music.getSingerId(),music.getMusicId()};
		return executeUpdate(sql, params);
	}

	@Override
	public int delMusic(int musicId) {
		String sql ="delete from music where musicId=?";
		Object[] params={musicId};
		return executeUpdate(sql, params);
	}

	@Override
	public PagerData queryByPage(MusicQuery musicQuery, int curPage, int pageSize) {
		//根据条件拼接where语句
		String sqlWhere = "";
		List<Object> params = new ArrayList<Object>();
		
		//歌曲名字条件不为空
		if(!MyUtil.isNull(musicQuery.getMusicName())){
			sqlWhere +=" and mu.musicName like ? ";
			params.add(musicQuery.getMusicName());
		}
		//歌曲分类条件不为空
		if(musicQuery.getMusicTypeId()!=null){
			sqlWhere += " and mut.musicTypeId = ? ";
			params.add(musicQuery.getMusicTypeId());
		}
		//专辑条件不为空
		if(musicQuery.getSpecialId()!=null){
			sqlWhere += " and sp.specialId = ? ";
			params.add(musicQuery.getSpecialId());
		}
		//歌单条件不为空
		if(musicQuery.getMusicListId()!=null){
			sqlWhere += " and mul.musicListId = ? ";
			params.add(musicQuery.getMusicListId());
		}
		//歌手条件不为空
		if(musicQuery.getSingerId()!=null){
			sqlWhere += " and si.singerId = ? ";
			params.add(musicQuery.getSingerId());
		}
		//获取带条件查询的总记录数
		String sqlTotal ="select "
				+ " count(*) "
				+ " from music as mu join musictype as mut on mu.musicTypeId=mut.musicTypeId "
				+ " join special as sp on mu.specialId=sp.specialId "
				+ " join musiclist as mul on mu.musicListId=mul.musicListId "
				+ " join singer as si on mu.singerId=si.singerId"
				+ "   where 1=1 "+sqlWhere;
		int totalCount = jdbcTemplate.queryForInt(sqlTotal,params.toArray());
		//根据总记录数获取总页数
		int totalPage = totalCount%pageSize==0 ?
				        totalCount/pageSize    :
				        totalCount/pageSize+1;
		String sql ="select mu.musicId,mu.musicName,mu.timeLength,mu.musicMp3,mu.musicPhoto,"
				+ "mut.musicTypeName,sp.specialName,mul.musicListName,si.singerName "
				+ "from music as mu join musictype as mut on mu.musicTypeId=mut.musicTypeId "
				+ "join special as sp on mu.specialId=sp.specialId "
				+ "join musiclist as mul on mu.musicListId=mul.musicListId "
				+ "join singer as si on mu.singerId=si.singerId"+sqlWhere
				+ " order by mu.musicId"
				+ " limit ?,?";
		//计算分页查询的开始位置
		int start = (curPage-1)*pageSize;
		
		params.add(start);
		params.add(pageSize);
		List<Map<String,Object>> dataList = executeQuery(sql,params.toArray());
		//封装分页数据对象
		PagerData pd = new PagerData();
		//当前第几页
		pd.setCurPage(curPage);
		//每页几条
		pd.setPageSize(pageSize);
		//总记录数
		pd.setTotalCount(totalCount);
		//总页数
		pd.setTotalPage(totalPage);
		//当前页数据记录
		pd.setDataList(dataList);
		return pd;
	}

}
